package com.elegantmall.dao;

import com.elegantmall.dto.SalesReportDTO;
import com.elegantmall.pojo.OrderPojo;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.util.Date;
import java.util.List;
import java.util.Map;

@Mapper
@Repository
public interface SalesReportDao {
    
    /**
     * 获取指定时间范围内的所有订单
     */
    @Select("SELECT * FROM orders WHERE create_time BETWEEN #{startDate} AND #{endDate}")
    List<OrderPojo> getOrdersByDateRange(@Param("startDate") Date startDate, @Param("endDate") Date endDate);
    
    /**
     * 获取指定卖家和时间范围内的所有订单
     */
    @Select("SELECT * FROM orders WHERE seller_name = #{sellerName} AND create_time BETWEEN #{startDate} AND #{endDate}")
    List<OrderPojo> getOrdersBySellerAndDateRange(
            @Param("sellerName") String sellerName, 
            @Param("startDate") Date startDate, 
            @Param("endDate") Date endDate);
    
    /**
     * 获取商品销售数量统计
     */
    @Select("SELECT o.goods_id, g.goods_name, g.category, SUM(o.number) as total_sold, " +
            "SUM(o.number * g.price) as total_revenue " +
            "FROM orders o JOIN goods g ON o.goods_id = g.goods_id " +
            "WHERE o.create_time BETWEEN #{startDate} AND #{endDate} " +
            "GROUP BY o.goods_id, g.goods_name, g.category " +
            "ORDER BY total_sold DESC")
    List<Map<String, Object>> getProductSalesStats(
            @Param("startDate") Date startDate, 
            @Param("endDate") Date endDate);
    
    /**
     * 获取特定卖家的商品销售数量统计
     */
    @Select("SELECT o.goods_id, g.goods_name, g.category, SUM(o.number) as total_sold, " +
            "SUM(o.number * g.price) as total_revenue " +
            "FROM orders o JOIN goods g ON o.goods_id = g.goods_id " +
            "WHERE o.seller_name = #{sellerName} AND o.create_time BETWEEN #{startDate} AND #{endDate} " +
            "GROUP BY o.goods_id, g.goods_name, g.category " +
            "ORDER BY total_sold DESC")
    List<Map<String, Object>> getSellerProductSalesStats(
            @Param("sellerName") String sellerName,
            @Param("startDate") Date startDate, 
            @Param("endDate") Date endDate);
    
    /**
     * 获取类别销售统计
     */
    @Select("SELECT g.category, SUM(o.number) as total_sold, " +
            "SUM(o.number * g.price) as total_revenue " +
            "FROM orders o JOIN goods g ON o.goods_id = g.goods_id " +
            "WHERE o.create_time BETWEEN #{startDate} AND #{endDate} " +
            "GROUP BY g.category " +
            "ORDER BY total_revenue DESC")
    List<Map<String, Object>> getCategorySalesStats(
            @Param("startDate") Date startDate, 
            @Param("endDate") Date endDate);
    
    /**
     * 获取指定类别的订单
     */
    @Select("SELECT o.* FROM orders o JOIN goods g ON o.goods_id = g.goods_id " +
            "WHERE g.category = #{category} AND o.create_time BETWEEN #{startDate} AND #{endDate}")
    List<OrderPojo> getOrdersByCategoryAndDateRange(
            @Param("category") String category,
            @Param("startDate") Date startDate, 
            @Param("endDate") Date endDate);

    /**
     * 获取总销售额
     */
    @Select("SELECT SUM(o.number * g.price) as total_sales " +
            "FROM orders o JOIN goods g ON o.goods_id = g.goods_id " +
            "WHERE o.create_time BETWEEN #{startDate} AND #{endDate}")
    Double getTotalSales(
            @Param("startDate") Date startDate, 
            @Param("endDate") Date endDate);
    
    /**
     * 获取总订单数
     */
    @Select("SELECT COUNT(*) FROM orders WHERE create_time BETWEEN #{startDate} AND #{endDate}")
    Integer getTotalOrders(
            @Param("startDate") Date startDate, 
            @Param("endDate") Date endDate);
} 